﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using VideoClassLibrary;
using VideoClassLibrary.Model;
using System.Web;

namespace VideoClassLibrary.BusinessLogicLayer
{
    public class UserModule
    {
        Database db = VDataBase.CreateDatabase();
        /// <summary>
        /// MD5加密
        /// </summary>
        /// <returns></returns>
        public static string MD5Enctrypt(string pwd)
        {
            return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(pwd, "md5");
        }

        /// <summary>
        /// 是否可以注册以参数loginName为登录名的用户
        /// </summary>
        /// <param name="loginName">待检验的登录名</param>
        /// <returns>可以注册为true,否则为false</returns>
        public bool CanRegistUser(string loginName)
        {
            string strSql = "select count(1) FROM v_user where Stu_LoginName=@loginName";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql);
            db.AddInParameter(command, "loginName", DbType.AnsiString, loginName);
            return !(int.Parse(db.ExecuteScalar(command).ToString()) > 0);
        }
        /// <summary>
        /// 用户登录方法
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <param name="userType">用户类型 0 运营管理员 1 学校管理员  其他学生用户</param>
        /// <returns></returns>
        public object UserLogin(string name, string pwd, int userType)
        {
            string sql = string.Empty;
            if (userType == 0 || userType == 1)
            {
                return AdminUserLogin(name, pwd);
            }
            else
            {
                sql = "select count(1) from V_user  where VU_loginname=@name and VU_Password=@pwd";
                Database db = VDataBase.CreateDatabase();
                DbCommand command = db.GetSqlStringCommand(sql);
                db.AddInParameter(command, "name", DbType.String, name);
                db.AddInParameter(command, "pwd", DbType.String, MD5Enctrypt(pwd));
                V_user user = null;
                DataSet dsResult = db.ExecuteDataSet(command);
                if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
                {
                    DataRow dr = dsResult.Tables[0].Rows[0];
                    user = new V_user();
                    user.create_time = dr["create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
                    user.ID = int.Parse(dr["ID"].ToString());
                    user.SchoolID = dr["SchoolID"] == DBNull.Value ? 0 : int.Parse(dr["SchoolID"].ToString());
                    user.VU_loginname = dr["VU_loginname"].ToString();
                    user.VU_Password = dr["VU_Password"].ToString();
                    user.VU_realname = dr["VU_realname"].ToString();
                    user.VU_mobile = dr["VU_mobile"].ToString();
                    user.VU_email = dr["VU_email"].ToString();
                    dsResult = null;
                }
                return user;
            }

        }

        /// <summary>
        /// 管理员登陆
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public V_adminUser AdminUserLogin(string name, string pwd)
        {
            V_adminUser admin = null;
            string sql = "Select  ID,Create_time,Update_time,VA_loginname,VA_password,VA_realname,VA_mobile,VA_email,VA_type,SchoolID,VA_IsEnable  FROM V_adminUser  where VA_loginname=@name and VA_password=@pwd and VA_IsEnable=1";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(sql);
            db.AddInParameter(command, "name", DbType.String, name);
            db.AddInParameter(command, "pwd", DbType.String, MD5Enctrypt(pwd));
            DataSet dsResult = db.ExecuteDataSet(command);
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                admin = new V_adminUser();
                admin.Create_time = dr["Create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
                admin.ID = int.Parse(dr["ID"].ToString());
                admin.SchoolID = dr["SchoolID"] == DBNull.Value ? 0 : int.Parse(dr["SchoolID"].ToString());
                admin.VA_type = int.Parse(dr["VA_type"].ToString());
                admin.VA_loginname = dr["VA_loginname"].ToString();
                admin.VA_password = dr["VA_password"].ToString();
                admin.VA_realname = dr["VA_realname"].ToString();
                admin.VA_mobile = dr["VA_mobile"].ToString();
                admin.VA_email = dr["VA_email"].ToString();
                admin.Va_IsEnable =int.Parse(dr["Va_IsEnable"].ToString());
                dsResult = null;
            }
            return admin;


        }
        /// <summary>
        /// 添加管理员
        /// </summary>
        /// <param name="admin"></param>
        /// <returns>add by 桑慧娜</returns>
        public int AddAdminUser(V_adminUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_adminUser(");
            strSql.Append("Create_time,Update_time,VA_loginname,VA_password,VA_realname,VA_mobile,VA_email,VA_type,SchoolID,VA_IsEnable)");

            strSql.Append(" values (");
            strSql.Append("@Create_time,@Update_time,@VA_loginname,@VA_password,@VA_realname,@VA_mobile,@VA_email,@VA_type,@SchoolID,@VA_IsEnable)");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Create_time", DbType.DateTime, model.Create_time);
            db.AddInParameter(dbCommand, "Update_time", DbType.DateTime, model.Update_time);
            db.AddInParameter(dbCommand, "VA_loginname", DbType.AnsiString, model.VA_loginname);
            db.AddInParameter(dbCommand, "VA_password", DbType.AnsiString, MD5Enctrypt(model.VA_password));
            db.AddInParameter(dbCommand, "VA_realname", DbType.AnsiString, model.VA_realname);
            db.AddInParameter(dbCommand, "VA_mobile", DbType.AnsiString, model.VA_mobile);
            db.AddInParameter(dbCommand, "VA_email", DbType.AnsiString, model.VA_email);
            db.AddInParameter(dbCommand, "VA_type", DbType.Int32, model.VA_type);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "VA_IsEnable", DbType.Int32, model.Va_IsEnable);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;

        }
        /// <summary>
        /// 添加学生信息
        /// </summary>
        /// <returns></returns> 
        public int AddUser(V_user user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_user(");
            strSql.Append("update_time,create_time,SchoolID,VU_loginname,VU_Password,VU_realname,VU_mobile,VU_email,IsAvailable,IsBuyingAccount)");

            strSql.Append(" values (");
            strSql.Append("@Update_time,@Create_time,@SchoolID,@VU_loginname,@VU_Password,@VU_realname,@VU_mobile,@VU_email,@IsAvailable,@IsBuyingAccount)");
            strSql.Append(";select @@IDENTITY");

            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Create_time", DbType.DateTime, user.create_time);
            db.AddInParameter(dbCommand, "Update_time", DbType.DateTime, user.update_time);
            db.AddInParameter(dbCommand, "VU_loginname", DbType.AnsiString, user.VU_loginname);
            db.AddInParameter(dbCommand, "VU_Password", DbType.AnsiString, user.VU_Password);
            db.AddInParameter(dbCommand, "VU_realname", DbType.AnsiString, user.VU_realname);
            db.AddInParameter(dbCommand, "VU_mobile", DbType.AnsiString, user.VU_mobile);
            db.AddInParameter(dbCommand, "VU_email", DbType.AnsiString, user.VU_email);
            db.AddInParameter(dbCommand, "IsAvailable", DbType.Boolean, user.IsAvailable);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, user.SchoolID);
            db.AddInParameter(dbCommand, "IsBuyingAccount", DbType.Int32, user.IsBuyingAccount);
            int result;
            try
            {
                int.TryParse(db.ExecuteScalar(dbCommand).ToString(), out result);
            }
            catch
            {
                result = 0;
            }
            return result;
        }


        /// <summary>
        /// 根据ID修改学生信息
        /// </summary>
        /// <returns></returns> 
        public int UpdateUser(V_user user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update V_user set  update_time=@Update_time,SchoolID=@SchoolID,VU_loginname=@VU_loginname,");
            if (user.VU_Password == null || user.VU_Password == "")
            {
                strSql.Append("VU_realname=@VU_realname,VU_mobile=@VU_mobile,VU_email=@VU_email where  ID=@id");
            }
            else
            {
                strSql.Append(" VU_Password=@VU_Password,VU_realname=@VU_realname,VU_mobile=@VU_mobile,VU_email=@VU_email where  ID=@id");
            }

            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "id", DbType.Int32, user.ID);
            db.AddInParameter(dbCommand, "Update_time", DbType.DateTime, user.update_time);
            db.AddInParameter(dbCommand, "VU_loginname", DbType.AnsiString, user.VU_loginname);
            if (user.VU_Password == null || user.VU_Password == "")
            { }
            else { db.AddInParameter(dbCommand, "VU_Password", DbType.AnsiString, MD5Enctrypt(user.VU_Password)); }

            db.AddInParameter(dbCommand, "VU_realname", DbType.AnsiString, user.VU_realname);
            db.AddInParameter(dbCommand, "VU_mobile", DbType.AnsiString, user.VU_mobile);
            db.AddInParameter(dbCommand, "VU_email", DbType.AnsiString, user.VU_email);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, user.SchoolID);
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            {
                user = userLogin(user.VU_loginname, user.VU_Password);
                return 1;
            }
            else
            {
                return -1;
            }
        }
        public DataSet GetUserListByWhere(string whereSql, int schoolId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [ID],[update_time],[create_time],[SchoolID],[VU_loginname],[VU_Password],[VU_realname],[VU_mobile],[VU_email],IsAvailable,IsBuyingAccount");
            strSql.Append(" FROM V_user where 1=1  ");
            if (schoolId != 0)
            {
                strSql.Append("  and SchoolID= " + schoolId);
            }

            if (whereSql != "")
            {
                strSql.Append(whereSql);
            }
            strSql.Append("  order by create_time desc ");
            Database db = VDataBase.CreateDatabase();

            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        /// <summary>
        ///  根据学生姓名模糊查找所有学生信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetSchoolListByName(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [ID],[update_time],[create_time],[SchoolID],[VU_loginname],[VU_Password],[VU_realname],[VU_mobile],[VU_email],IsAvailable");
            strSql.Append(" FROM V_user where VU_realname like '%" + name + "%'");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            return db.ExecuteDataSet(dbCommand);
        }
        /// <summary>
        ///根据ID删除学生信息
        /// </summary>
        /// <returns></returns> 
        public int DeleteUser(int uid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  V_user where ID=@id");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "id", DbType.Int32, uid);
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            {
                return 1;
            }
            else
            {
                return -1;
            }
        }
        /// <summary>
        ///  用户登陆并且可以记录用户信息
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public V_user userLogin(string name, string pwd)
        {
            V_user user = new V_user();
            string sql = "select [ID],[update_time],[create_time],[SchoolID],[VU_realname],[VU_mobile],[VU_email],IsAvailable from [V_user] where [VU_loginname]=@Aname and [VU_Password]=@Apwd";
            SqlParameter par = new SqlParameter("@Aname", name);
            SqlParameter par1 = new SqlParameter("@Apwd", MD5Enctrypt(pwd));
            VDataBase vb = new VDataBase();
            SqlDataReader read = vb.GetDataByReader(sql, false, par, par1);
            if (read.HasRows)
            {
                read.Read();
                user.ID = Convert.ToInt32(read[0]);
                user.update_time = read[1] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(read[1]);
                user.create_time = read[2] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(read[2]);
                user.SchoolID = Convert.ToInt32(read[3].ToString());
                user.VU_realname = read[4].ToString();
                user.VU_mobile = read[5].ToString();
                user.VU_email = read[6].ToString();
                user.IsAvailable = Convert.ToBoolean(read[7]);
                read.Close();
                return user;
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// 用户登录返回学员 为记录登录日志 add by yxq
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="pwd"></param>
        /// <param name="schoolID"></param>
        /// <returns></returns>
        public V_user UserLoginBySchoolID(string uname, string pwd, int schoolID)
        {

            string sql = "select [ID],[update_time],[create_time],[SchoolID],[VU_realname],[VU_mobile],[VU_email],IsAvailable from [V_user] where [VU_loginname]=@name and [VU_Password]=@pwd and SchoolID=@SchoolID";
            using (DbCommand command = db.GetSqlStringCommand(sql))
            {
                db.AddInParameter(command, "@name", DbType.String, uname);
                db.AddInParameter(command, "@pwd", DbType.AnsiString, MD5Enctrypt(pwd));
                db.AddInParameter(command, "@SchoolID", DbType.Int32, schoolID);
                DataSet ds = db.ExecuteDataSet(command);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    var query = from s in ds.Tables[0].AsEnumerable()
                                select new V_user
                                {
                                    ID = s.Field<int>("ID"),
                                    VU_realname = s.Field<string>("VU_realname"),
                                    SchoolID = s.Field<int>("SchoolID"),
                                    IsAvailable = s.Field<bool>("IsAvailable"),
                                    VU_email = s.Field<string>("VU_email")

                                };
                    return query.FirstOrDefault();
                }
            }
            return null;
        }

        /// <summary>
        /// 用户登录日志添加 add by yxq
        /// </summary>
        /// <param name="user"></param>
        public void AddUserLog(V_user user)
        {
            V_UserLoginLog log = new V_UserLoginLog();
            log.UserID = user.ID;
            log.UserName = user.VU_realname;
            log.SchoolID = user.SchoolID;
            log.Login_Time = DateTime.Now;
            log.Login_IP = HttpContext.Current.Request.UserHostAddress;
            log.Login_Client = HttpContext.Current.Request.Browser.Platform;
            log.Login_Browser = HttpContext.Current.Request.Browser.Browser + HttpContext.Current.Request.Browser.Version;
            log.Logout_Time = DateTime.Now;
            UserModule.AddUserLoginLog(log);
        }

        /// <summary>
        /// 添加用户登录的日志
        /// </summary>
        /// <param name="model"></param>
        /// <returns>add by zhangqian</returns>
        public static int AddUserLoginLog(V_UserLoginLog model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_UserLoginLog(");
            strSql.Append("UserID,UserName,SchoolID,Login_Time,Login_IP,Login_Client,Login_Browser,Logout_Time)");

            strSql.Append(" values (");
            strSql.Append("@UserID,@UserName,@SchoolID,@Login_Time,@Login_IP,@Login_Client,@Login_Browser,@Logout_Time)");
            Database db = VDataBase.CreateDatabase();
            try
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
                db.AddInParameter(dbCommand, "UserID", DbType.Int32, model.UserID);
                db.AddInParameter(dbCommand, "UserName", DbType.AnsiString, model.UserName);
                db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
                db.AddInParameter(dbCommand, "Login_Time", DbType.DateTime, model.Login_Time);
                db.AddInParameter(dbCommand, "Login_IP", DbType.AnsiString, model.Login_IP);
                db.AddInParameter(dbCommand, "Login_Client", DbType.AnsiString, model.Login_Client);
                db.AddInParameter(dbCommand, "Login_Browser", DbType.AnsiString, model.Login_Browser);
                db.AddInParameter(dbCommand, "Logout_Time", DbType.DateTime, model.Logout_Time);
                return db.ExecuteNonQuery(dbCommand);
            }
            catch
            {
                return 0;
            }


        }

        /// <summary>
        /// 记录用户观看视频的日志
        /// </summary>
        /// <param name="model"></param>
        /// <returns>add by zhangqian</returns>
        public static int AddUserViewVideoLog(V_UserViewVideoLog model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_UserViewVideoLog(");
            strSql.Append("UserID,UserName,View_time,courselistID,SchoolID)");

            strSql.Append(" values (");
            strSql.Append("@UserID,@UserName,@View_time,@courselistID,@SchoolID)");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "UserID", DbType.Int32, model.UserID);
            db.AddInParameter(dbCommand, "UserName", DbType.AnsiString, model.UserName);
            db.AddInParameter(dbCommand, "View_time", DbType.DateTime, model.View_time);
            db.AddInParameter(dbCommand, "courselistID", DbType.Int32, model.courselistID);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }
        /// <summary>
        ///根据ID获取学生实体
        /// </summary>
        /// <returns></returns> 
        public V_user GetUserModelById(int userId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM V_user  ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "ID", DbType.Int32, userId);
            DataSet dsResult = db.ExecuteDataSet(command);
            V_user model = null;

            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                model = new V_user();
                model.create_time = dr["Create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
                model.ID = int.Parse(dr["ID"].ToString());
                model.SchoolID = int.Parse(dr["SchoolID"].ToString());
                model.VU_loginname = dr["VU_loginname"].ToString();
                model.VU_Password = dr["VU_Password"].ToString();
                model.VU_realname = dr["VU_realname"].ToString();
                model.VU_mobile = dr["VU_mobile"].ToString();
                model.VU_email = dr["VU_email"].ToString();
                model.IsAvailable =Convert.ToBoolean(dr["IsAvailable"].ToString());
                dsResult = null;
            }
            return model;
        }

        /// <summary>
        /// 根据学员ID修改他自己的密码
        /// </summary>
        /// <param name="admin"></param>
        /// <returns>add by 桑慧娜</returns>

        public int UpdateUserPwd(string PWd, string sid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update V_user set  update_time=@Update_time,VU_Password=@VU_Password where  ID=@id");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "id", DbType.Int32, sid);
            db.AddInParameter(dbCommand, "Update_time", DbType.DateTime, DateTime.Now);
            db.AddInParameter(dbCommand, "VU_Password", DbType.AnsiString, MD5Enctrypt(PWd));
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            {
                return 1;
            }
            else
            {
                return -1;
            }
        }


        /// <summary>
        /// 密码找回 自动随机生成密码
        /// </summary>
        /// <param name="admin"></param>
        /// <returns>add by yxq</returns>
        public int UpdateUserPwd(V_user user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update V_user set  update_time=@Update_time,VU_Password=@VU_Password where  VU_loginname=@VU_loginname");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "VU_loginname", DbType.String, user.VU_loginname);
            db.AddInParameter(dbCommand, "Update_time", DbType.DateTime, DateTime.Now);
            db.AddInParameter(dbCommand, "VU_Password", DbType.AnsiString, MD5Enctrypt(user.VU_Password));
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            {
                return 1;
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 判断用户和邮箱是否是注册的时候一致
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool CheckUnameByEmail(V_user user)
        {

            string sql = "select count(*) from V_user where VU_loginname=@name and  VU_email=@email";
            Database vb = VDataBase.CreateDatabase();
            using (DbCommand command = vb.GetSqlStringCommand(sql))
            {
                vb.AddInParameter(command, "@name", DbType.String, user.VU_loginname);
                vb.AddInParameter(command, "@email", DbType.String, user.VU_email);
                if (Convert.ToInt32(vb.ExecuteScalar(command)) > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }


        /// <summary>
        /// 判断是否可以注册
        /// </summary>
        public bool IfNameExist(string uName)
        {
            string sql = "select count(*) from V_user where VU_loginname=@name ";
            SqlParameter username = new SqlParameter("@name", uName);
            VDataBase vb = new VDataBase();
            object result = vb.GetDataByScalar(sql, false, username);
            int n = Convert.ToInt32(result);
            if (n != 0)
            {
                return false;

            }
            else
                return true;

        }

        /// <summary>
        /// 判断是否可以注册
        /// </summary>
        public bool IfNameExist(int schoolId,string uName)
        {
            string sql = "select count(*) from V_user where VU_loginname=@name and SchoolID=@SchoolID";
            SqlParameter username = new SqlParameter("@name", uName);
            SqlParameter sId = new SqlParameter("@SchoolID", schoolId);
            VDataBase vb = new VDataBase();
            object result = vb.GetDataByScalar(sql, false, username, sId);
            int n = Convert.ToInt32(result);
            if (n != 0)
            {
                return false;

            }
            else
                return true;

        }

        /// <summary>
        /// 修改用户状态
        /// </summary>
        /// <param name="schoolId">所属学校</param>
        /// <param name="isAvailable">状态</param>
        /// <param name="uid">用户</param>
        /// <returns></returns>
        public DataSet UpdateUserIsAvailable(int schoolId, int isAvailable, int uid)
        {
            string sql = "update V_user set  IsAvailable=@isAvailable where  ID=@uid and schoolid=@schoolId";
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(dbCommand, "uid", DbType.Int32, uid);
            db.AddInParameter(dbCommand, "IsAvailable", DbType.Boolean, isAvailable);
            db.AddInParameter(dbCommand, "schoolid", DbType.Int32, schoolId);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            //插入修改日志
            V_user user = GetUserModelById(uid);
            V_User_Log userLog = new V_User_Log();
            userLog.UserId = uid;
            userLog.SchoolId = schoolId;
            userLog.OperateDateTime = DateTime.Now;
            userLog.IsEnable = isAvailable;
            new UserModule().StartOrStopUserLog(userLog);
            return ds;
        }

        /// <summary>
        /// 批量注册用户，并分配培训班
        /// </summary>
        /// <param name="userList"></param>
        /// <param name="schoolId"></param>
        /// <returns>add by zhangqian</returns>
        public List<ReturnResult> QuantityRegisterUser(List<V_user> userList, int schoolId)
        {
            //遍历用户列表，判断哪些是已经存在的用户，哪些是已经分配过该培训班的用户，哪些是新用户

            Database db = VDataBase.CreateDatabase();

            List<ReturnResult> resultList = new List<ReturnResult>();
            foreach (V_user user in userList)
            {
                ReturnResult result = new ReturnResult();
                if (user == null)
                {
                    result.IsSuccess = false;
                    result.ErrorMsg = "";
                    resultList.Add(result);
                    continue;
                }
                if (user.VU_loginname == "")
                {
                    result.IsSuccess = false;
                    result.ErrorMsg = "用户登录名不能为空";
                    resultList.Add(result);
                    continue;
                }
                if (user.TrainingClassIdList == 0)
                {
                    result.IsSuccess = false;
                    result.ErrorMsg = "指定的培训班不存在";
                    resultList.Add(result);
                    continue;
                }
                DbCommand dbCommand = db.GetStoredProcCommand("Proc_AddUserAndTrainingClassRelation");
                db.AddInParameter(dbCommand, "UserID", DbType.Int32, user.ID);
                db.AddInParameter(dbCommand, "VU_loginname", DbType.String, user.VU_loginname);
                db.AddInParameter(dbCommand, "VU_Password", DbType.String, MD5Enctrypt(user.VU_loginname));
                db.AddInParameter(dbCommand, "VU_realname", DbType.String, user.VU_realname);
                db.AddInParameter(dbCommand, "VU_mobile", DbType.String, user.VU_mobile);
                db.AddInParameter(dbCommand, "VU_email", DbType.String, user.VU_email);
                //db.AddInParameter(dbCommand, "IsAvailable", DbType.Boolean,user.IsAvailable);
                db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, schoolId);
                db.AddInParameter(dbCommand, "TrainingClassId", DbType.Int32, user.TrainingClassIdList);

                try
                {
                    int retValue = 0;
                    if (int.TryParse(db.ExecuteScalar(dbCommand).ToString(), out retValue))
                    {
                        result.IsSuccess = true;
                        if (retValue == 0)
                        {
                            result.IsSuccess = false;
                            result.ErrorMsg = "不允许重复分配";
                        }
                        resultList.Add(result);

                    }
                }
                catch (Exception ex)
                {
                    result.IsSuccess = false;
                    result.ErrorMsg = ex.Message;
                    resultList.Add(result);
                }
            }
            return resultList;

        }

        public int StartOrStopUserLog(V_User_Log log)
        {
            int result = 0;
            Database db = VDataBase.CreateDatabase();
            string sqlStr = "Insert into V_User_Log ([SchoolID],[UserID],[OperateDateTime],[IsEnable]) values(@SchoolID,@UserID,@OperateDateTime,@IsEnable)";

            DbCommand command = db.GetSqlStringCommand(sqlStr);
            db.AddInParameter(command, "@SchoolID", DbType.Int32, log.SchoolId);
            db.AddInParameter(command, "@UserID", DbType.Int32, log.UserId);
            db.AddInParameter(command, "@IsEnable", DbType.Int32, log.IsEnable);
            db.AddInParameter(command, "@OperateDateTime", DbType.DateTime,DateTime.Now);
            try
            {
                result = db.ExecuteNonQuery(command);
            }
            catch
            {
                result = 0;
            }
            return result;
        }

        public void GetUserLogInfo(DataRow dataRow,ref List<SchoolReport> schoolReports   )
        {
            StringBuilder tableStr = new StringBuilder();
            string sqlStr = "select count(*) FROM [V_user] a join [V_school] b on a.SchoolID = b.ID where b.ID = @ID ";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(sqlStr);
            db.AddInParameter(command, "ID", DbType.Int32, dataRow["ID"]);  
            command.CommandText = sqlStr;
            int userCount = int.Parse(db.ExecuteScalar(command).ToString());
            sqlStr = "select count(*) FROM [V_user] a join [V_school] b on a.SchoolID = b.ID where b.ID = @ID and IsAvailable = 1 ";
            command.CommandText = sqlStr;
            int availableCount = int.Parse(db.ExecuteScalar(command).ToString());
            //sqlStr = "select count(*) FROM [V_user] a join [V_school] b on a.SchoolID = b.ID where b.ID = @ID and IsAvailable = 0  ";
            //command.CommandText = sqlStr;
            //int unAvailableCount = int.Parse(db.ExecuteScalar(command).ToString());
            sqlStr = "select COUNT(*) from v_user c where c.ID in( select distinct userID from V_UserLoginLog where userID in (select a.ID FROM [V_user] a join [V_school] b on a.SchoolID = b.ID where b.ID = @ID ) and  ( Login_Time>='" + DateTime.Today.ToString("yyyy-MM-dd") + "' and Logout_Time < '" + DateTime.Today.AddDays(1).ToString("yyyy-MM-dd") + "'))";
            command.CommandText = sqlStr;
            int loginUser = int.Parse(db.ExecuteScalar(command).ToString());
            SchoolReport schoolReport = new SchoolReport()
                                            {
                                                AvailableCount = availableCount,
                                                LoginUser = loginUser,
                                                SchoolId = Convert.ToInt32(dataRow["ID"]),
                                                UserCount = userCount,
                                                SchoolName = dataRow["VS_name"].ToString()
            };
            schoolReports.Add(schoolReport);
          
            //tableStr.Append("<tr><td ><font color=\"red\">用户总数：</font>" + userCount.ToString() + "</td>");
            //tableStr.Append("<td> <font color=\"red\">启动用户数：</font>" + availableCount.ToString() + "</td>");
            //tableStr.Append("<td> <font color=\"red\">今天活跃用户数：</font>" + loginUser.ToString() + "</td></tr>");


            //return tableStr.ToString();
        }
    }
}
